﻿CREATE PROCEDURE [dbo].[utl_AssignExecToSP]
@UserName [sysname]
AS
BEGIN
	DECLARE @ProcedureName     sysname
	DECLARE @ID                INT
	DECLARE @SQL               NVARCHAR(MAX)
	
	DECLARE @StoredProcedures  TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, ProcedureName sysname)
	
	INSERT INTO @StoredProcedures
	  (
	    ProcedureName
	  )
	SELECT ProcedureName
	FROM   dbo.fn_CRUDProcs()
	
	SELECT @ID = MIN(ID)
	FROM   @StoredProcedures
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ProcedureName = ProcedureName
	    FROM   @StoredProcedures
	    WHERE  ID = @ID
	    
	    SET @SQL = 'GRANT EXECUTE ON ' + dbo.fn_QuoteName(@ProcedureName) + ' TO [' + @UserName + ']'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ID = MIN(ID)
	    FROM   @StoredProcedures
	    WHERE  ID > @ID
	END
END

